package dao;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Date;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;

/*
import org.apache.commons.dbutils.ResultSetHandler;
import org.apache.commons.dbutils.handlers.ArrayHandler;
import org.apache.commons.dbutils.handlers.ArrayListHandler;

*/

import dbPool.ConnectionDAO;
import beans.Customer;
import beans.CustomerLogin;
import beans.Customer_Rentals;

public class Customer_RentalsDAO {

	ConnectionDAO conn = new ConnectionDAO();
    Connection con = null;
    Statement stmt = null;
    
    static {
    	try{
    		new dbPool.JDCConnectionDriver("com.mysql.jdbc.Driver","jdbc:mysql://localhost/virtualreel", "root", "root"); 
    	}catch(Exception e){
    		
    	}
    }
    
    public Connection getConnection() throws SQLException {
        return DriverManager.getConnection("jdbc:jdc:jdcpool");
    }
    
    public int createCustomer_Rentals(Customer_Rentals cBean){
    	try{
    		con = getConnection();
    		Statement stmt = con.createStatement();
    		String query = "INSERT INTO `customerrentals`(c_id, m_id, cr_date_out, cr_date_returned, cr_rental_id) VALUES('"+cBean.getC_Id()+"','"+cBean.getM_Id()+"','"+cBean.getCR_Date_Out()+"','"+cBean.getCR_Date_Returned()+"','"+cBean.getCR_Rental_Id()+"');";
    		stmt.executeUpdate(query);
    		return 0;
    	}
    	catch(Exception se){
    		Logger.getLogger(Customer_RentalsDAO.class.getName()).log(Level.SEVERE, "save failed", se);
    		return 1;
    	}
    	finally {
    		try {
				con.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
    	}
    }
    
    public Customer_Rentals[] retrieveCustomer_Rentals(String C_Id, String Column_Name){
    	
    	Customer_Rentals[] cBeanTwo = null;
    	
    	try{
    		con = getConnection();
    		Statement stmt = con.createStatement();
    		Customer_Rentals cBeanOne = new Customer_Rentals();
    		String query = "SELECT "+Column_Name+" FROM `customerrentals` WHERE `c_id` LIKE '"+C_Id+"';";
    		ResultSet rs = stmt.executeQuery(query);
    		int i=0;
    		ArrayList<Customer_Rentals> cBeanThree = new ArrayList<Customer_Rentals>(); 
    		while(rs.next()){
    			//System.out.println(rs.getString("CR_Rental_Id"));
    			cBeanOne.setC_Id(rs.getString("C_Id"));
    			cBeanOne.setM_Id(rs.getString("M_Id"));
    			cBeanOne.setCR_Date_Out(rs.getDate("CR_Date_Out"));
    			cBeanOne.setCR_Date_Returned(rs.getDate("CR_Date_Returned"));
    			cBeanOne.setCR_Rental_Id(rs.getString("CR_Rental_Id"));
    			cBeanThree.add(cBeanOne);
    			i++;
    		}
    		//System.out.println(cBeanThree);
    		cBeanTwo = new Customer_Rentals[cBeanThree.size()];
    		cBeanThree.toArray(cBeanTwo);
    		return cBeanTwo;
    	}
    	catch(Exception se){
    		Logger.getLogger(Customer_RentalsDAO.class.getName()).log(Level.SEVERE, "save failed", se);
    		return cBeanTwo;
    	}
    	finally {
    		try {
				con.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
    	}
    }
    
    public int updateCustomer_Rentals(String CR_Rental_Id, String key, String value){
    	try{
    		con = getConnection();
    		Statement stmt = con.createStatement();
    		String query = "UPDATE `customerrentals` SET  `"+key+"` =  '"+value+"' WHERE  `customerrentals`.`cr_rental_id` =  '"+CR_Rental_Id+"';";
    		stmt.executeUpdate(query);
    		return 0;
    	}
    	catch(Exception se){
    		Logger.getLogger(Customer_RentalsDAO.class.getName()).log(Level.SEVERE, "save failed", se);
    		return 1;
    	}
    	finally {
    		try {
				con.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
    	}
    }
    
    public int deleteCustomer_Rentals(String CR_Rental_Id) {
    	try{
    		con = getConnection();
    		Statement stmt = con.createStatement();
    		String query = "DELETE FROM `customerrentals` WHERE `customerrentals`.`cr_rental_id` = '"+CR_Rental_Id+"';";
    		stmt.executeUpdate(query);
    		return 0;
    	}
    	catch(Exception se){
    		Logger.getLogger(Customer_RentalsDAO.class.getName()).log(Level.SEVERE, "save failed", se);
    		return 1;
    	}
    	finally {
    		try {
				con.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
    	}
    }
    
    @SuppressWarnings("deprecation")
	public static void main(String args[]){
    	
    	/*//trial method one
    	Customer_Rentals crBean = new Customer_Rentals();
    	Date date = new Date(20110512);
    	
    	crBean.setCR_Rental_Id("cr1-db-0001");
    	crBean.setC_Id("000-00-0001");
    	crBean.setM_Id("mv1-db-0001");
    	crBean.setCR_Date_Out(date);
    	crBean.setCR_Date_Returned(date);
    	
    	Customer_RentalsDAO crdao = new Customer_RentalsDAO();
    	crdao.createCustomer_Rentals(crBean);
    	
    	for(int i=2; i<10; i++){
    		crBean.setCR_Rental_Id("cr1-db-000"+i);
    		crdao.createCustomer_Rentals(crBean);
    	}*/
    	
    	//trial method two
    	//Customer_Rentals[] crBean = new Customer_Rentals[5];
    	Customer_RentalsDAO crdao = new Customer_RentalsDAO();
    	Customer_Rentals[] crBean = crdao.retrieveCustomer_Rentals("000-00-0001", "*");
    	for(int i=0; i<crBean.length; i++){
    		System.out.println(crBean[i].getCR_Rental_Id());
    	}
    	
    	/*//trial method three
    	Customer_RentalsDAO crdao = new Customer_RentalsDAO();
    	int result = crdao.updateCustomer_Rentals("cr1-db-0003", "c_id", "000-00-0004");
    	System.out.println(result);*/
    	
    	/*//trial method four
    	Customer_RentalsDAO crdao = new Customer_RentalsDAO();
    	int result = crdao.deleteCustomer_Rentals("cr1-db-0008");
    	System.out.println(result);*/
    }
	
}
